﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports extranet.BE
Imports MySql.Data.MySqlClient

Public Class DAVaga
  Private objBD As New BancoDeDados
  Public objQuery As New BEQuery

  Sub New()
  End Sub

  Sub New(ByVal booCondicaoSimilar As Boolean)
    objQuery.Where.CondicaoSimilar = booCondicaoSimilar
  End Sub

  Public Sub Cadastra(ByRef objBEVaga As BEVaga)
    Try
      objBD.duplicaAspasSimples(objBEVaga, GetType(BEVaga))
      objBD.ExecutaNonQuery("INSERT INTO ext_vagas (codigo, areaid, titulo, descricao, ativo, datacadastro) " & _
                            "VALUES ('" & objBEVaga.Codigo & "', '" & _
                                          objBEVaga.AreaVaga & "', '" & _
                                          objBEVaga.Titulo & "', '" & _
                                          objBEVaga.Descricao & "', '" & _
                                          objBEVaga.Estado & "', '" & _
                                          Now().ToString("yyyy/MM/dd HH:mm") & "');" & _
                            "SELECT LAST_INSERT_ID();")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.Cadastra(): " & ex.Message)
    End Try
  End Sub

  Public Sub Atualiza(ByVal objBEVaga As BEVaga)
    Try
      objBD.duplicaAspasSimples(objBEVaga, GetType(BEVaga))
      objBD.ExecutaNonQuery("UPDATE ext_vagas " & _
                            "SET codigo='" & objBEVaga.Codigo & "', " & _
                                "areaid='" & objBEVaga.AreaVaga & "', " & _
                                "titulo='" & objBEVaga.Titulo & "', " & _
                                "descricao='" & objBEVaga.Descricao & "', " & _
                                "ativo='" & objBEVaga.Estado & "' " & _
                            "WHERE id=" & objBEVaga.Id & ";")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.Atualiza(): " & ex.Message)
    End Try
  End Sub

  Public Sub RelacionaVagaProva(ByVal objBEVaga As BEVaga)
    Try
      objBD.duplicaAspasSimples(objBEVaga, GetType(BEVaga))
      objBD.ExecutaNonQuery("UPDATE ext_vagas " & _
                            "SET provaId=" & objBEVaga.Prova.Id & " " & _
                            "WHERE id=" & objBEVaga.Id & ";")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.RelacionaVagaProva(): " & ex.Message)
    End Try
  End Sub

  Public Sub Exclui(ByVal objBEVaga As BEVaga)
    Try
      objBD.ExecutaNonQuery("DELETE FROM ext_vagas WHERE id='" & objBEVaga.Id & "';")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.Exclui(): " & ex.Message)
    End Try
  End Sub

  Public Function SelecionaVaga(ByVal objBEVaga As BEVaga) As BEVaga
    Try
      objQuery.Where.CondicaoSimilar = False
      Dim objBEVagas As List(Of BEVaga) = Seleciona(objBEVaga)
      If objBEVagas.Count > 0 Then
        objBEVaga = objBEVagas(0)
      Else
        objBEVaga = Nothing
      End If
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.Seleciona(): " & ex.Message)
    End Try
    Return objBEVaga
  End Function

  Public Function Seleciona(ByVal objBEVaga As BEVaga) As List(Of BEVaga)
    Dim objBEVagas As List(Of BEVaga) = New List(Of BEVaga)
    Try
      objBD.duplicaAspasSimples(objBEVaga, GetType(BEVaga))
      objQuery._Select = "SELECT v.*, (SELECT count(*) from ext_rel_candidato_vagas WHERE vagaid=v.id) qtdeCandidatos FROM ext_vagas v"
      If objBEVaga IsNot Nothing Then
        If objBEVaga.Id > 0 Then objQuery.Where.AdicionarCondicaoWhere("v.id", objBEVaga.Id, False)
        If objBEVaga.AreaVaga > 0 Then objQuery.Where.AdicionarCondicaoWhere("v.areaid", objBEVaga.AreaVaga, False)
        If objBEVaga.Estado >= 0 Then objQuery.Where.AdicionarCondicaoWhere("v.ativo", objBEVaga.Estado, False)
        objQuery.Where.AdicionarCondicaoWhere("v.codigo", objBEVaga.Codigo)
        objQuery.Where.AdicionarCondicaoWhere("v.titulo", objBEVaga.Titulo)
      End If
      objBEVagas = SelecionaVagas()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.Seleciona(): " & ex.Message)
    End Try
    Return objBEVagas
  End Function

  Private Function SelecionaVagas() As List(Of BEVaga)
    Dim objBEVagas As List(Of BEVaga) = New List(Of BEVaga)
    Dim objBEVaga As BEVaga
    Dim strProvaId As String
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBEVaga = New BEVaga()
            With objBEVaga
              .Id = objSQLDReader("id")
              .Codigo = objSQLDReader("codigo")
              .Titulo = objSQLDReader("titulo")
              .Descricao = objSQLDReader("descricao")
              .Estado = objSQLDReader("ativo")
              .AreaVaga = objSQLDReader("areaid")
              .QtdeCandidatos = objSQLDReader("qtdeCandidatos")

              strProvaId = objSQLDReader("provaid").ToString()
              .Prova = New BEProva(Integer.Parse(IIf(strProvaId.Length > 0, strProvaId, "0")))
              objBEVaga.DataCadastro = objSQLDReader("datacadastro")
            End With
            objBEVagas.Add(objBEVaga)
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.SelecionaVagas(): " & ex.Message)
    End Try
    Return objBEVagas
  End Function

  Public Sub AdicionarCandidatos(ByVal objBEVaga As BEVaga)
    Dim strSQL As String = ""
    Try
      For Each objBECandidato As BECandidato In objBEVaga.Candidatos
        strSQL &= "INSERT INTO ext_rel_candidato_vagas (vagaid, candidatoid) VALUES (" & _
                  objBEVaga.Id & ", " & objBECandidato.Id & ");"
      Next
      Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
      objComando.ExecuteNonQuery()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.AdicionarCandidatos(): " & ex.Message)
    End Try

  End Sub

  Public Sub RemoverCandidatos(ByVal objBEVaga As BEVaga)
    Dim strSQL As String = ""
    Try
      For Each objBECandidato As BECandidato In objBEVaga.Candidatos
        strSQL &= "DELETE FROM ext_rel_candidato_vagas WHERE vagaid=" & objBEVaga.Id & _
                                                       " AND candidatoid=" & objBECandidato.Id & ";"
      Next
      Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
      objComando.ExecuteNonQuery()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.RemoverCandidato(): " & ex.Message)
    End Try
  End Sub

End Class
